﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Z.BulkOperations;
using Z.Dapper.Plus;

namespace Dapper.Sharding
{
    public abstract class IDatabase
    {
        public IDatabase(string name, IClient client)
        {
            Name = name;
            Client = client;
            AutoCreateTable = client.AutoCreateTable;
            AutoCompareTableColumn = client.AutoCompareTableColumn;
            AutoCompareTableColumnLength = client.AutoCompareTableColumnLength;
            AutoCompareTableColumnDelete = client.AutoCompareTableColumnDelete;
        }

        #region dapper method

        public int Execute(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.Execute(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.Execute(sql, param, val.Item2, timeout);
        }

        public object ExecuteScalar(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.ExecuteScalar(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.ExecuteScalar(sql, param, val.Item2, timeout);
        }

        public T ExecuteScalar<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.ExecuteScalar<T>(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.ExecuteScalar<T>(sql, param, val.Item2, timeout);
        }

        public dynamic QueryFirstOrDefault(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.QueryFirstOrDefault(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.QueryFirstOrDefault(sql, param, val.Item2, timeout);
        }

        public T QueryFirstOrDefault<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.QueryFirstOrDefault<T>(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.QueryFirstOrDefault<T>(sql, param, val.Item2, timeout);
        }

        public List<dynamic> Query(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.Query(sql, param, commandTimeout: timeout).AsList();
            }
            var val = tran.GetVal(this);
            return val.Item1.Query(sql, param, val.Item2, commandTimeout: timeout).AsList();
        }

        public List<T> Query<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.Query<T>(sql, param, commandTimeout: timeout).AsList();
            }
            var val = tran.GetVal(this);
            return val.Item1.Query<T>(sql, param, val.Item2, commandTimeout: timeout).AsList();
        }

        public void QueryMultiple(string sql, object param = null, Action<SqlMapper.GridReader> onReader = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var conn = GetConn();
                using var reader = conn.QueryMultiple(sql, param, commandTimeout: timeout);
                onReader?.Invoke(reader);
                return;
            }
            var val = tran.GetVal(this);
            using (var reader = val.Item1.QueryMultiple(sql, param, val.Item2, commandTimeout: timeout))
            {
                onReader?.Invoke(reader);
            }
        }

        public DataTable QueryDataTable(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.QueryDataTable(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.QueryDataTable(sql, param, val.Item2, timeout);
        }

        public DataSet QueryDataSet(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = GetConn();
                return cnn.QueryDataSet(sql, param, commandTimeout: timeout);
            }
            var val = tran.GetVal(this);
            return val.Item1.QueryDataSet(sql, param, val.Item2, timeout);
        }

        #endregion

        #region dapper method async

        public async Task<int> ExecuteAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.ExecuteAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.ExecuteAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<object> ExecuteScalarAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.ExecuteScalarAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.ExecuteScalarAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.ExecuteScalarAsync<T>(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.ExecuteScalarAsync<T>(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<dynamic> QueryFirstOrDefaultAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.QueryFirstOrDefaultAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.QueryFirstOrDefaultAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.QueryFirstOrDefaultAsync<T>(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.QueryFirstOrDefaultAsync<T>(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<List<dynamic>> QueryAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return (await cnn.QueryAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken)).AsList();
            }
            var val = await tran.GetValAsync(this);
            return (await val.Item1.QueryAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken)).AsList();
        }

        public async Task<List<T>> QueryAsync<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return (await cnn.QueryAsync<T>(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken)).AsList();
            }
            var val = await tran.GetValAsync(this);
            return (await val.Item1.QueryAsync<T>(sql, param, val.Item2, timeout, cancellationToken: cancellationToken)).AsList();
        }

#if NET5_0_OR_GREATER
        public async IAsyncEnumerable<T> QueryUnbufferedAsync<T>(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, [EnumeratorCancellation] CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                var list0 = cnn.QueryUnbufferedAsync<T>(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
                await foreach (var item in list0)
                {
                    yield return item;
                }
            }

            var val = await tran.GetValAsync(this);
            var list = val.Item1.QueryUnbufferedAsync<T>(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
            await foreach (var item in list)
            {
                yield return item;
            }
        }

        public async IAsyncEnumerable<dynamic> QueryUnbufferedAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, [EnumeratorCancellation] CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                var list0 = cnn.QueryUnbufferedAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
                await foreach (var item in list0)
                {
                    yield return item;
                }
            }

            var val = await tran.GetValAsync(this);
            var list = val.Item1.QueryUnbufferedAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
            await foreach (var item in list)
            {
                yield return item;
            }
        }
#endif


        public async Task QueryMultipleAsync(string sql, object param = null, Action<SqlMapper.GridReader> onReader = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var conn = await GetConnAsync();
                using var reader = await conn.QueryMultipleAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
                onReader?.Invoke(reader);
                return;
            }
            var val = await tran.GetValAsync(this);
            using (var reader = await val.Item1.QueryMultipleAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken))
            {
                onReader?.Invoke(reader);
            }
        }

        public async Task<DataTable> QueryDataTableAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.QueryDataTableAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.QueryDataTableAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }

        public async Task<DataSet> QueryDataSetAsync(string sql, object param = null, DistributedTransaction tran = null, int? timeout = null, CancellationToken cancellationToken = default)
        {
            if (tran == null || DbType == DataBaseType.ClickHouse)
            {
                using var cnn = await GetConnAsync();
                return await cnn.QueryDataSetAsync(sql, param, commandTimeout: timeout, cancellationToken: cancellationToken);
            }
            var val = await tran.GetValAsync(this);
            return await val.Item1.QueryDataSetAsync(sql, param, val.Item2, timeout, cancellationToken: cancellationToken);
        }


        #endregion

        #region z.dapper.plus method

        public void BulkInsert<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            var key = DapperPlusUtils.Map<T>(tableName, DbType);
            if (tran == null)
            {
                using var cnn = GetConn();
                using var tr = cnn.BeginTransaction();
                try
                {
                    tr.UseBulkOptions(option =>
                    {
                        if (batchSize.HasValue)
                        {
                            option.BatchSize = batchSize.Value;
                        }
                        action(option);

                    }).BulkInsert(key, modelList);
                    tr.Commit();
                }
                catch
                {
                    tr.Rollback();
                    throw;
                }
            }
            else
            {
                var val = tran.GetVal(this);
                val.Item2.UseBulkOptions(option =>
                {
                    if (batchSize.HasValue)
                    {
                        option.BatchSize = batchSize.Value;
                    }
                    action(option);

                }).BulkInsert(key, modelList);
            }
        }

        public void BulkUpdate<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            var key = DapperPlusUtils.Map<T>(tableName, DbType);
            if (tran == null)
            {
                using var cnn = GetConn();
                using var tr = cnn.BeginTransaction();
                try
                {
                    tr.UseBulkOptions(option =>
                    {
                        if (batchSize.HasValue)
                        {
                            option.BatchSize = batchSize.Value;
                        }
                        action(option);

                    }).BulkUpdate(key, modelList);
                    tr.Commit();
                }
                catch
                {
                    tr.Rollback();
                    throw;
                }
            }
            else
            {
                var val = tran.GetVal(this);
                val.Item2.UseBulkOptions(option =>
                {
                    if (batchSize.HasValue)
                    {
                        option.BatchSize = batchSize.Value;
                    }
                    action(option);

                }).BulkUpdate(key, modelList);
            }
        }

        public void BulkDelete<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            var key = DapperPlusUtils.Map<T>(tableName, DbType);
            if (tran == null)
            {
                using var cnn = GetConn();
                using var tr = cnn.BeginTransaction();
                try
                {
                    tr.UseBulkOptions(option =>
                    {
                        if (batchSize.HasValue)
                        {
                            option.BatchSize = batchSize.Value;
                        }
                        action(option);

                    }).BulkDelete(key, modelList);
                    tr.Commit();
                }
                catch
                {
                    tr.Rollback();
                    throw;
                }
            }
            else
            {
                var val = tran.GetVal(this);
                val.Item2.UseBulkOptions(option =>
                {
                    if (batchSize.HasValue)
                    {
                        option.BatchSize = batchSize.Value;
                    }
                    action(option);

                }).BulkDelete(key, modelList);
            }
        }

        public void BulkMerge<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            var key = DapperPlusUtils.Map<T>(tableName, DbType);
            if (tran == null)
            {
                using var cnn = GetConn();
                using var tr = cnn.BeginTransaction();
                try
                {
                    tr.UseBulkOptions(option =>
                    {
                        if (batchSize.HasValue)
                        {
                            option.BatchSize = batchSize.Value;
                        }
                        action(option);

                    }).BulkMerge(key, modelList);
                    tr.Commit();
                }
                catch
                {
                    tr.Rollback();
                    throw;
                }
            }
            else
            {
                var val = tran.GetVal(this);
                val.Item2.UseBulkOptions(option =>
                {
                    if (batchSize.HasValue)
                    {
                        option.BatchSize = batchSize.Value;
                    }
                    action(option);

                }).BulkMerge(key, modelList);
            }
        }

        #endregion

        #region z.dapper.plus method async

        public Task BulkInsertAsync<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            return Task.Run(() =>
            {
                BulkInsert(tableName, modelList, action, tran, batchSize);
            });
        }

        public Task BulkUpdateAsync<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            return Task.Run(() =>
            {
                BulkUpdate(tableName, modelList, action, tran, batchSize);
            });
        }

        public Task BulkDeleteAsync<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            return Task.Run(() =>
            {
                BulkDelete(tableName, modelList, action, tran, batchSize);
            });
        }

        public Task BulkMergeAsync<T>(string tableName, IEnumerable<T> modelList, Action<BulkOperation> action, DistributedTransaction tran = null, int? batchSize = null) where T : class
        {
            return Task.Run(() =>
            {
                BulkMerge(tableName, modelList, action, tran, batchSize);
            });
        }

        #endregion

        #region protected method

        protected readonly LockManager Locker = new LockManager();

        protected readonly LockManagerAsync LockerAsync = new LockManagerAsync();

        protected readonly ConcurrentDictionary<string, object> TableCache = new ConcurrentDictionary<string, object>();

        protected readonly ConcurrentDictionary<string, object> TableCache2 = new ConcurrentDictionary<string, object>();

        protected abstract ITable<T> CreateITable<T>(string name) where T : class;

        #endregion

        #region public method

        public string Name { get; }

        public IClient Client { get; }

        public bool AutoCreateTable { get; set; }

        public bool AutoCompareTableColumn { get; set; }

        public bool AutoCompareTableColumnLength { get; set; }

        public bool AutoCompareTableColumnDelete { get; set; }

        public DataBaseType DbType
        {
            get
            {
                return Client.DbType;
            }
        }

        public DataBaseVersion DbVersion
        {
            get
            {
                return Client.DbVersion;
            }
        }

        public virtual void CreateTable<T>(string name)
        {
            var script = GetTableScript<T>(name);
            Execute(script);
        }

        public virtual Task CreateTableAsync<T>(string name)
        {
            var script = GetTableScript<T>(name);
            return ExecuteAsync(script);
        }

        public ITable<T> GetTable<T>(string name) where T : class
        {
            var exists = TableCache.TryGetValue(name, out var val);
            if (!exists)
            {
                lock (Locker.GetObject(name))
                {
                    if (!TableCache.ContainsKey(name))
                    {
                        if (AutoCreateTable)
                        {
                            #region 创建表、对比表

                            if (!ExistsTable(name))
                            {
                                CreateTable<T>(name);
                            }
                            else if (AutoCompareTableColumn)
                            {
                                IEnumerable<string> dbColumns;
                                var tableEntity = ClassToTableEntityUtils.Get<T>(DbType, DbVersion);
                                var manager = GetTableManager(name);
                                List<ColumnEntity> list = null;

                                if (!AutoCompareTableColumnLength)
                                {
                                    dbColumns = GetTableColumnList(name);
                                }
                                else
                                {
                                    list = manager.GetColumnEntityList();
                                    dbColumns = list.Select(s => s.Name);
                                }

                                foreach (var item in tableEntity.ColumnList)
                                {
                                    if (!dbColumns.Any(a => a.ToLower().Equals(item.Name.ToLower())))
                                    {
                                        manager.AddColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                    }
                                    else if (AutoCompareTableColumnLength)
                                    {
                                        if (item.CsType == typeof(string) && item.Length != 0)
                                        {
                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (dbItem != null && item.Length != dbItem.Length)
                                            {
                                                manager.ModifyColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                            }
                                        }
                                        else if (item.CsType == typeof(DateTime)
                                            || item.CsType == typeof(DateTime?)
                                            || item.CsType == typeof(DateTimeOffset)
                                            || item.CsType == typeof(DateTimeOffset?))
                                        {
                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (dbItem != null && item.Length != dbItem.Length)
                                            {
                                                manager.ModifyColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                            }
                                        }
                                        else if (item.CsType == typeof(decimal) || item.CsType == typeof(decimal?))
                                        {
                                            if (item.Length == 0)
                                            {
                                                continue;
                                            }

                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (item.Scale == 0)
                                            {
                                                if (item.Length != dbItem.Length)
                                                {
                                                    manager.ModifyColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                }
                                            }
                                            else
                                            {
                                                if (item.Scale.ToString().EndsWith("0"))
                                                {
                                                    if (item.Length != dbItem.Length || item.Scale != dbItem.Scale)
                                                    {
                                                        manager.ModifyColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                    }
                                                }
                                                else
                                                {
                                                    var len = Convert.ToDouble($"{item.Length}.{item.Scale}");
                                                    if (len != dbItem.Length)
                                                    {
                                                        manager.ModifyColumn(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }

                                if (AutoCompareTableColumnDelete)
                                {
                                    foreach (var item in dbColumns)
                                    {
                                        if (!tableEntity.ColumnList.Any(a => a.Name.ToLower().Equals(item.ToLower())))
                                        {
                                            manager.DropColumn(item);
                                        }
                                    }
                                }
                            }

                            #endregion
                        }
                        TableCache.TryAdd(name, CreateITable<T>(name));
                    }
                }
                val = TableCache[name];
            }
            return (ITable<T>)val;
        }

        public async ValueTask<ITable<T>> GetTableAsync<T>(string name) where T : class
        {
            var exists = TableCache.TryGetValue(name, out var val);
            if (!exists)
            {
                var locker = LockerAsync.GetObjct(name);
                await locker.WaitAsync();
                try
                {
                    if (!TableCache.ContainsKey(name))
                    {
                        if (AutoCreateTable)
                        {
                            #region 创建表、对比表

                            if (!await ExistsTableAsync(name))
                            {
                                await CreateTableAsync<T>(name);
                            }
                            else if (AutoCompareTableColumn)
                            {
                                IEnumerable<string> dbColumns;
                                var tableEntity = ClassToTableEntityUtils.Get<T>(DbType, DbVersion);
                                var manager = GetTableManager(name);
                                List<ColumnEntity> list = null;

                                if (!AutoCompareTableColumnLength)
                                {
                                    dbColumns = await GetTableColumnListAsync(name);
                                }
                                else
                                {
                                    list = await manager.GetColumnEntityListAsync();
                                    dbColumns = list.Select(s => s.Name);
                                }

                                foreach (var item in tableEntity.ColumnList)
                                {
                                    if (!dbColumns.Any(a => a.ToLower().Equals(item.Name.ToLower())))
                                    {
                                        await manager.AddColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                    }
                                    else if (AutoCompareTableColumnLength)
                                    {
                                        if (item.CsType == typeof(string) && item.Length != 0)
                                        {
                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (dbItem != null && item.Length != dbItem.Length)
                                            {
                                                await manager.ModifyColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                            }
                                        }
                                        else if (item.CsType == typeof(DateTime)
                                            || item.CsType == typeof(DateTime?)
                                            || item.CsType == typeof(DateTimeOffset)
                                            || item.CsType == typeof(DateTimeOffset?))
                                        {
                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (dbItem != null && item.Length != dbItem.Length)
                                            {
                                                await manager.ModifyColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                            }
                                        }
                                        else if (item.CsType == typeof(decimal) || item.CsType == typeof(decimal?))
                                        {

                                            if (item.Length == 0)
                                            {
                                                continue;
                                            }

                                            var dbItem = list.FirstOrDefault(f => f.Name.ToLower().Equals(item.Name.ToLower()));
                                            if (item.Scale == 0)
                                            {
                                                if (item.Length != dbItem.Length)
                                                {
                                                    await manager.ModifyColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                }
                                            }
                                            else
                                            {
                                                if (item.Scale.ToString().EndsWith("0"))
                                                {
                                                    if (item.Length != dbItem.Length || item.Scale != dbItem.Scale)
                                                    {
                                                        await manager.ModifyColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                    }
                                                }
                                                else
                                                {
                                                    var len = Convert.ToDouble($"{item.Length}.{item.Scale}");
                                                    if (len != dbItem.Length)
                                                    {
                                                        await manager.ModifyColumnAsync(item.Name, item.CsType, item.Length, item.Comment, item.DbType, item.Scale);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }

                                if (AutoCompareTableColumnDelete)
                                {
                                    foreach (var item in dbColumns)
                                    {
                                        if (!tableEntity.ColumnList.Any(a => a.Name.ToLower().Equals(item.ToLower())))
                                        {
                                            await manager.DropColumnAsync(item);
                                        }
                                    }
                                }
                            }

                            #endregion
                        }
                        TableCache.TryAdd(name, CreateITable<T>(name));
                    }
                }
                finally
                {
                    locker.Release();
                }
                val = TableCache[name];
            }
            return (ITable<T>)val;
        }

        public ITable<T> GetTableExist<T>(string name) where T : class
        {
            var key = typeof(T).FullName + "@" + name;
            var exists = TableCache2.TryGetValue(key, out var val);
            if (!exists)
            {
                lock (Locker.GetObject(key))
                {
                    if (!TableCache2.ContainsKey(key))
                    {
                        TableCache2.TryAdd(key, CreateITable<T>(name));
                    }
                }
                val = TableCache2[key];
            }
            return (ITable<T>)val;
        }

        public void GeneratorTableFile(string savePath, List<string> tableList = null, string nameSpace = "Model", string suffix = "", bool partialClass = false, bool classNameToUpper = false, bool columnToUpper = false, bool nullDateTime = true)
        {
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            if (tableList == null || tableList.Count == 0)
            {
                tableList = GetTableList().ToList();
            }
            foreach (var name in tableList)
            {
                var entity = GetTableEntityFromDatabase(name, columnToUpper);
                if (DbType == DataBaseType.ClickHouse)
                {
                    entity.IsIdentity = false;
                    entity.PrimaryKey = entity.ColumnList[0].Name;
                }
                string className;
                if (classNameToUpper)
                {
                    className = name.FirstCharToUpper() + suffix;
                }
                else
                {
                    className = name + suffix;
                }
                var sb = new StringBuilder();
                sb.Append("using System;");
                sb.AppendLine();
                sb.Append("using Dapper.Sharding;");
                sb.AppendLine();
                sb.AppendLine();
                sb.Append($"namespace {nameSpace}");
                sb.AppendLine();
                sb.Append("{");
                sb.AppendLine();
                if (entity.IndexList != null)
                {
                    var indexList = entity.IndexList.Where(w => w.Type != IndexType.PrimaryKey);
                    foreach (var item in indexList)
                    {
                        sb.Append($"    [Index(\"{item.Name}\", \"{item.Columns}\", {item.StringType})]");
                        sb.AppendLine();
                    }
                }
                if (!string.IsNullOrEmpty(entity.Comment))
                {
                    entity.Comment = entity.Comment.Replace("\r", "").Replace("\n", "");
                }
                sb.Append($"    [Table(\"{entity.PrimaryKey}\", {entity.IsIdentity.ToString().ToLower()}, \"{entity.Comment}\")]");
                sb.AppendLine();
                if (partialClass)
                {
                    sb.Append($"    public partial class {className}");
                    sb.AppendLine();
                }
                else
                {
                    sb.Append($"    public class {className}");
                    sb.AppendLine();
                }
                sb.Append("    {");
                sb.AppendLine();
                foreach (var item in entity.ColumnList)
                {
                    if (item.Length != 0 || !string.IsNullOrEmpty(item.Comment))
                    {
                        if (!string.IsNullOrEmpty(item.Comment))
                        {
                            item.Comment = item.Comment.Replace("\r", "").Replace("\n", "");
                        }
                        if (item.CsType == typeof(decimal) && item.Scale > 0)
                        {
                            sb.Append($"        [Column({item.Length}, \"{item.Comment}\", scale: {item.Scale})]");
                        }
                        else
                        {
                            sb.Append($"        [Column({item.Length}, \"{item.Comment}\")]");
                        }
                        sb.AppendLine();
                    }

                    if (nullDateTime)
                    {
                        if (item.CsStringType == "DateTime")
                        {
                            item.CsStringType = "DateTime?";
                        }
                        else if (item.CsStringType == "DateTimeOffset")
                        {
                            item.CsStringType = "DateTimeOffset?";
                        }
                    }

                    if (DbType == DataBaseType.ClickHouse)
                    {
                        sb.Append("        public " + item.CsStringType + " " + item.Name + " { get; set; }");
                        sb.AppendLine();
                    }
                    else
                    {
                        sb.Append("        public " + item.CsStringType + " " + item.Name + " { get; set; }");
                        sb.AppendLine();
                    }

                    if (item != entity.ColumnList.Last())
                    {
                        sb.AppendLine();
                    }
                }
                sb.Append("    }");
                sb.AppendLine();
                sb.Append("}");
                var path = Path.Combine(savePath, $"{className}.cs");
                File.WriteAllText(path, sb.ToString(), Encoding.UTF8);
            }
        }

        public void GeneratorDbContextFile(string savePath, string nameSpace = "Model", string modelNameSpace = "Model", bool classNameToUpper = false, string modelSuffix = "", bool proSuffix = false)
        {
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            var tableList = GetTableList().ToList();
            var sb = new StringBuilder();
            sb.Append("using Dapper.Sharding;");
            sb.AppendLine();
            if (nameSpace != modelNameSpace)
            {
                sb.Append($"using {modelNameSpace};");
                sb.AppendLine();
                sb.AppendLine();
            }
            else
            {
                sb.AppendLine();
            }
            sb.Append($"namespace {nameSpace}");
            sb.AppendLine();
            sb.Append("{");
            sb.AppendLine();
            sb.Append("    public class DbContext");
            sb.AppendLine();
            sb.Append("    {");
            sb.AppendLine();

            sb.Append("        public IClient Client { get; }");
            sb.AppendLine();
            sb.Append("        public string DbName { get; }");
            sb.AppendLine();
            sb.Append("        public IDatabase Db => Client.GetDatabase(DbName);");
            sb.AppendLine();
            sb.AppendLine();
            sb.Append("        public DbContext(IClient client, string dbName)");
            sb.AppendLine();
            sb.Append("        {");
            sb.AppendLine();
            sb.Append("            Client = client;");
            sb.AppendLine();
            sb.Append("            DbName = dbName;");
            sb.AppendLine();
            sb.Append("        }");

            sb.AppendLine();
            sb.AppendLine();

            foreach (var name in tableList)
            {
                string className;
                if (classNameToUpper)
                {
                    className = name.FirstCharToUpper() + modelSuffix;
                }
                else
                {
                    className = name + modelSuffix;
                }

                string className2;
                if (proSuffix)
                {
                    className2 = className;
                }
                else
                {
                    className2 = name.FirstCharToUpper();
                }
                sb.Append($"        public ITable<{className}> {className2} => Db.GetTable<{className}>(\"{name}\");");
                sb.AppendLine();
            }
            sb.Append("    }");
            sb.AppendLine();
            sb.Append("}");
            var path = Path.Combine(savePath, "DbContext.cs");
            File.WriteAllText(path, sb.ToString(), Encoding.UTF8);
        }

        public void ClearCache(string tablename = null)
        {
            if (!string.IsNullOrEmpty(tablename))
            {
                TableCache.TryRemove(tablename, out _);
            }
            else
            {
                TableCache.Clear();
            }

        }

        #endregion

        #region abstract method

        public abstract string ConnectionString { get; set; }

        public abstract DbConnection GetConn();

        public abstract Task<DbConnection> GetConnAsync();

        public abstract void DropTable(string name);

        public abstract void TruncateTable(string name);

        public abstract Task TruncateTableAsync(string name, CancellationToken cancellationToken = default);

        public abstract List<string> GetTableList();

        public abstract List<string> GetTableColumnList(string name);

        public abstract Task<List<string>> GetTableColumnListAsync(string name);

        public abstract bool ExistsTable(string name);

        public abstract Task<bool> ExistsTableAsync(string name);

        public abstract string GetTableScript<T>(string name);

        public abstract TableEntity GetTableEntityFromDatabase(string name, bool firstCharToUpper = false);

        public abstract ITableManager GetTableManager(string name);

        public abstract void OptimizeTable(string name, bool final = false, bool deduplicate = false);

        public abstract void OptimizeTable(string name, string partition, bool final = false, bool deduplicate = false);

        public abstract void Vacuum();

        public abstract void SetTableComment(string name, string comment);

        public abstract void SetTablespace(string table, string tablespace);

        public abstract Task SetTablespaceAsync(string table, string tablespace);

        #endregion


        #region IQuery

        public IQuery AsQuery(string tableName)
        {
            IQuery query;
            if (DbType == DataBaseType.MySql)
            {
                query = new MySqlQuery(this);
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                query = new PostgreQuery(this);
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                query = new SQLiteQuery(this);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                query = new OracleQuery(this);
            }
            else
            {
                query = new SqlServerQuery(this);
                tableName += " WITH(NOLOCK)";
            }
            query.sqlTable.Append(tableName);
            return query;
        }

        public IQuery AsQueryForUpdate(string tableName)
        {
            IQuery query;
            if (DbType == DataBaseType.MySql)
            {
                query = new MySqlQuery(this)
                {
                    forUpdate = " FOR UPDATE"
                };
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                query = new PostgreQuery(this)
                {
                    forUpdate = " FOR UPDATE"
                };
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                query = new SQLiteQuery(this);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                query = new OracleQuery(this)
                {
                    forUpdate = " FOR UPDATE"
                };
            }
            else
            {
                query = new SqlServerQuery(this);
                tableName += " WITH(UPDLOCK)";
            }
            query.sqlTable.Append(tableName);
            return query;
        }

        public IQuery AsQueryForUpdateNoWait(string tableName)
        {
            IQuery query;
            if (DbType == DataBaseType.MySql)
            {
                query = new MySqlQuery(this)
                {
                    forUpdate = " FOR UPDATE NOWAIT"
                };
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                query = new PostgreQuery(this)
                {
                    forUpdate = " FOR UPDATE NOWAIT"
                };
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                query = new SQLiteQuery(this);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                query = new OracleQuery(this)
                {
                    forUpdate = " FOR UPDATE NOWAIT"
                };
            }
            else
            {
                query = new SqlServerQuery(this);
                tableName += " WITH(UPDLOCK,NOWAIT)";
            }
            query.sqlTable.Append(tableName);
            return query;
        }

        public IQuery AsQuery(string sql, string asName)
        {
            IQuery query;
            if (DbType == DataBaseType.MySql)
            {
                query = new MySqlQuery(this);
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                query = new PostgreQuery(this);
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                query = new SQLiteQuery(this);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                query = new OracleQuery(this);
            }
            else
            {
                query = new SqlServerQuery(this);
            }
            query.InitSql(sql, asName);
            return query;
        }

        #endregion

        #region IUnion

        public IUnion AsUnion()
        {
            if (DbType == DataBaseType.MySql)
            {
                return new MySqlUnion(this);
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                return new PostgreUnion(this);
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                return new SQLiteUnion(this);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                return new OracleUnion(this);
            }
            else
            {
                return new SqlServerUnion(this);
            }
        }

        public IUnion AsUnion(string sql)
        {
            if (DbType == DataBaseType.MySql)
            {
                return new MySqlUnion(this, sql);
            }
            else if (DbType == DataBaseType.Postgresql)
            {
                return new PostgreUnion(this, sql);
            }
            else if (DbType == DataBaseType.Sqlite)
            {
                return new SQLiteUnion(this, sql);
            }
            else if (DbType == DataBaseType.Oracle)
            {
                return new OracleUnion(this, sql);
            }
            else
            {
                return new SqlServerUnion(this, sql);
            }
        }

        #endregion
    }
}
